package com.slipper.core.database.support;

import com.slipper.core.database.IResolver;
import com.slipper.core.database.vo.FieldVO;
import com.slipper.core.database.vo.IndexFieldVO;
import com.slipper.core.database.vo.IndexVO;
import com.slipper.core.database.vo.TableVO;
import com.slipper.exception.CommonException;
import com.slipper.util.Tool;

import java.util.*;
import java.util.stream.Collectors;

public class MysqlResolver implements IResolver {
    private final static Map<String, String> dataTypeMap = new HashMap<>() {{
        put("tinyint", "整数");
        put("smallint", "整数");
        put("mediumint", "整数");
        put("int", "整数");
        put("bigint", "长整数");
        put("float", "小数");
        put("double", "小数");
        put("decimal", "小数");
        put("date", "日期型");
        put("datetime", "日期时间");
        put("timestamp", "日期时间");
        put("char", "定长字符");
        put("varchar", "字符");
        put("text", "大文本");
        put("longtext", "大文本");
        put("mediumtext", "大文本");
        put("blob", "二进制");
    }};

    private final static Map<String, String> fieldTypeMap = new HashMap<>() {{
        put("字符", "varchar");
        put("整数", "integer");
        put("小数", "decimal");
        put("日期", "varchar");
        put("时间", "bigint");
        put("图片", "varchar");
        put("视频", "varchar");
        put("附件", "varchar");
        put("联动", "varchar");
        put("布尔", "varchar(1)");
        put("大文本", "longtext");
        put("长整数", "bigint");
        put("二进制", "blob");
        put("日期型", "date");
        put("日期时间", "datetime");
        put("定长字符", "char");
    }};

    @Override
    public String tableTotalSQL(String tableName) {
        return "select count(*) " +
                "from information_schema.tables " +
                "where table_schema = (select database()) and lower(table_name) = '" + tableName + "'";
    }

    @Override
    public String tableListSQL(String tableName) {
        return "select " +
                "   lower(t.table_name) `table_name`, " +
                "   t.table_comment `table_comment` " +
                "from information_schema.tables t " +
                "where t.table_schema = (select database()) " +
                "   and t.table_schema = (select database()) " +
                "   " + (Tool.isNotBlank(tableName)
                ? "and (lower(t.table_name) like '%" + tableName + "%' " +
                "or t.table_comment like '%" + tableName + "%') "
                : "") +
                "order by t.table_name";
    }

    @Override
    public String tableColumnListSQL(String ...tableNames) {
        List<String> filterList = new ArrayList<>();
        for (String tableName : tableNames) {
            filterList.add(" lower(c.table_name) like '" + tableName + "' ");
        }
        return "select " +
                "    lower(t.table_name) `table_name`, " +
                "    lower(t.engine) `table_engine`, " +
                "    lower(t.table_collation) `table_collation`, " +
                "    lower(ca.character_set_name) `table_charset`, " +
                "    lower(c.column_name) `column_name`, " +
                "    lower(c.data_type) `column_type`, " +
                "    case when c.column_key='PRI' then '1' else '0' end `column_pk`, " +
                "    case when c.is_nullable='YES' then '1' else '0' end `column_null`, " +
                "    c.character_maximum_length `column_length`, " +
                "    c.numeric_precision `column_precision`, " +
                "    c.numeric_scale `column_scale`, " +
                "    c.ordinal_position `column_ordinal` " +
                "from information_schema.columns c,  " +
                "    information_schema.tables t,  " +
                "    information_schema.collation_character_set_applicability ca " +
                "where c.table_schema = (select database()) and t.table_schema = (select database()) " +
                "    and c.table_name=t.table_name and ca.collation_name = t.table_collation " +
                "    and (" + String.join(" or ", filterList) + ") " +
                "order by t.table_name, c.ordinal_position";
    }

    @Override
    public String tableIndexSQL(String ...tableNames) {
        List<String> filterList = new ArrayList<>();
        for (String tableName : tableNames) {
            filterList.add(" lower(s.table_name) like '" + tableName + "' ");
        }
        return "select " +
                "    lower(s.table_name) `table_name`, " +
                "    lower(s.index_name) `index_name`, " +
                "    case when s.non_unique=1 then '0' else '1' end `unique`, " +
                "    lower(s.column_name) `column_name`,     " +
                "    seq_in_index `ordinal` " +
                "from information_schema.statistics s " +
                "where s.table_schema = (select database()) and s.index_name != 'PRIMARY' " +
                "    and (" + String.join(" or ", filterList) + ") ";
    }

    @Override
    public String columnSQL(String tableName) {
        return """
            select
                lower(c.column_name) `name`,
                lower(c.data_type) `type`,
                c.character_maximum_length `length`,
                c.numeric_precision `precision`,
                c.numeric_scale `scale`,
                c.column_comment `comment` ,
                case when s.index_name = 'PRIMARY' then '1' else '0' end `pk`
            from information_schema.columns  c
                left join information_schema.statistics s
                    on s.table_schema=c.table_schema
                    and s.table_name=c.table_name
                    and s.column_name=c.column_name
            where c.table_schema = (select database()) and lower(c.table_name) = '%s'
            order by c.ordinal_position
            """.formatted(tableName);
    }

    @Override
    public String[] createTableDDL(TableVO table) {
        List<String> fieldList = new ArrayList<>();
        List<String> pkFieldList = new ArrayList<>();
        List<String> indexList = new ArrayList<>();

        table.getFieldList().sort(Comparator.comparingInt(FieldVO::getOrdinal));
        for (FieldVO field : table.getFieldList()) {
            String fieldType = fieldTypeMap.get(field.getType());
            if (Tool.isBlank(fieldType)) throw new CommonException("不支持此数据类型【" + field.getType() + "】！");

            String fieldSql = wrapSymbol(field.getName()) + " " + fieldType;
            fieldSql += switch (field.getType()) {
                case "小数" -> "(" + field.getLength() + ", " + field.getPrecision() + ")";
                case "字符", "字符串", "日期" -> "(" + (field.getLength() == null ? 20 : field.getLength()) + ")";
                case "图片", "视频", "附件", "联动" -> "(20)";
                default -> "";
            };
            fieldSql += field.isNullable() ? "" : " not null ";
            fieldList.add(fieldSql);

            if (field.isPrimaryKey()) {
                pkFieldList.add(field.getName());
            }
        }

        for (IndexVO index : table.getIndexList()) {
            String indexSql = "";
            if (index.isUnique()) {
                indexSql += "constraint " + wrapSymbol(index.getName()) + " unique";
            } else {
                indexSql += "index " + wrapSymbol(index.getName());
            }
            index.getFieldList().sort(Comparator.comparingInt(IndexFieldVO::getOrdinal));
            indexSql += "(";
            indexSql += index.getFieldList().stream()
                    .map(item -> wrapSymbol(item.getName()) + " " + (item.isAscending() ? "asc" : "desc"))
                    .collect(Collectors.joining(", "));
            indexSql += ")";
            indexList.add(indexSql);
        }

        String ddl = "create table " + wrapSymbol(table.getName()) + "(";
        ddl += String.join(", ", fieldList);
        if (!pkFieldList.isEmpty()) {
            ddl += ", primary key(";
            ddl += pkFieldList.stream().map(this::wrapSymbol).collect(Collectors.joining(", "));
            ddl += ")";
        }
        if (!indexList.isEmpty()) {
            ddl += ", " + String.join(", ", indexList);
        }
        ddl += ")";
        if (Tool.isNotBlank(table.getExtra())) {
            ddl += " " + table.getExtra();
        }

        return new String[] { ddl };
    }

    @Override
    public String[] createOrUpdateViewDDL(String viewName, String sql) {
        return new String[] {"create or replace view " + wrapSymbol(viewName) + " as " + sql};
    }

    @Override
    public String dropTableDDL(String tableName) {
        return "drop table " + wrapSymbol(tableName);
    }

    @Override
    public String dropViewDDL(String viewName) {
        return "drop view " + wrapSymbol(viewName);
    }

    @Override
    public String addFieldDDL(String tableName, FieldVO field) {
        String fieldType = fieldTypeMap.get(field.getType());
        if (Tool.isBlank(fieldType)) throw new CommonException("不支持此数据类型【" + field.getType() + "】！");

        String fieldSql = "alter table " + wrapSymbol(tableName) + " add ";
        fieldSql += wrapSymbol(field.getName()) + " " + fieldType;
        fieldSql += switch (field.getType()) {
            case "小数" -> "(" + field.getLength() + ", " + field.getPrecision() + ")";
            case "字符", "字符串", "日期" -> "(" + (field.getLength() == null ? 20 : field.getLength()) + ")";
            case "图片", "视频", "附件", "联动" -> "(20)";
            default -> "";
        };
        fieldSql += field.isNullable() ? "" : " not null ";
        return fieldSql;
    }

    @Override
    public String[] modifyFieldDDL(String tableName, FieldVO newField, FieldVO oldField) {
        String fieldType = fieldTypeMap.get(newField.getType());
        if (Tool.isBlank(fieldType)) throw new CommonException("不支持此数据类型【" + newField.getType() + "】！");

        String fieldSql = "alter table " + wrapSymbol(tableName);
        if (oldField != null && !oldField.getName().equals(newField.getName())) {
            fieldSql += " change column " + wrapSymbol(oldField.getName()) + " ";
        } else {
            fieldSql += " modify ";
        }
        fieldSql += wrapSymbol(newField.getName()) + " " + fieldType;
        fieldSql += switch (newField.getType()) {
            case "小数" -> "(" + newField.getLength() + ", " + newField.getPrecision() + ")";
            case "字符", "字符串", "日期" -> "(" + (newField.getLength() == null ? 20 : newField.getLength()) + ")";
            case "图片", "视频", "附件", "联动" -> "(20)";
            default -> "";
        };
        fieldSql += newField.isNullable() ? "" : " not null ";
        return new String[] { fieldSql };
    }

    @Override
    public String addIndexDDL(String tableName, IndexVO index) {
        String indexSql = "alter table " + wrapSymbol(tableName) + " add ";
        if (index.isUnique()) {
            indexSql += "constraint " + wrapSymbol(index.getName()) + " unique";
        } else {
            indexSql += "index " + wrapSymbol(index.getName());
        }
        index.getFieldList().sort(Comparator.comparingInt(IndexFieldVO::getOrdinal));
        indexSql += "(";
        indexSql += index.getFieldList().stream()
                .map(item -> wrapSymbol(item.getName()) + " " + (item.isAscending() ? "asc" : "desc"))
                .collect(Collectors.joining(", "));
        indexSql += ")";
        return indexSql;
    }

    @Override
    public String dropIndexDDL(String tableName, String indexName) {
        return "alter table " + wrapSymbol(tableName) +
                " drop index " + wrapSymbol(indexName);
    }

    @Override
    public void parseTable(Map<String, TableVO> tableMap, List<Map<String, Object>> fieldList) {
        fieldList.forEach(data -> {
            String tableName = (String) getValue("table_name", data);
            TableVO table = tableMap.get(tableName);
            if (table == null) {
                table = new TableVO();
                table.setName(tableName);

                String extra = "";
                String engine = (String) getValue("table_engine", data);
                String collate = (String) getValue("table_collation", data);
                String charset = (String) getValue("table_charset", data);
                extra += Tool.isNotBlank(engine) ? " ENGINE = " + engine : "";
                extra += Tool.isNotBlank(charset) ? " DEFAULT CHARACTER SET = " + charset : "";
                extra += Tool.isNotBlank(collate) ? " COLLATE = " + collate : "";
                table.setExtra(extra);

                tableMap.put(tableName, table);
            }

            FieldVO field = new FieldVO();
            field.setName((String) getValue("column_name", data));
            field.setType(changeType((String) getValue("column_type", data)));

            Object columnLength = getValue("column_length", data);
            Object columnPrecision = getValue("column_precision", data);
            Object columnScale = getValue("column_scale", data);

            Long length = null;
            Integer precision = null;
            if (columnLength != null) {
                length = Long.parseLong(columnLength.toString());
            } else if (columnPrecision != null) {
                length = Long.parseLong(columnPrecision.toString());
                if (columnScale != null) {
                    precision = Integer.parseInt(columnScale.toString());
                }
            }

            field.setLength(length);
            field.setPrecision(precision);
            field.setPrimaryKey((String) getValue("column_pk", data));
            field.setNullable((String) getValue("column_null", data));

            Object ordinal = getValue("column_ordinal", data);
            field.setOrdinal(ordinal == null ? 99 : Integer.parseInt(ordinal.toString()));
            table.addField(field);
        });
    }

    @Override
    public void parseIndex(Map<String, TableVO> tableMap, List<Map<String, Object>> indexList) {
        indexList.forEach(data -> {
            String tableName = (String) getValue("table_name", data);
            if (Tool.isBlank(tableName)) return;

            TableVO table = tableMap.get(tableName);
            String indexName = (String) getValue("index_name", data);
            if (Tool.isBlank(indexName)) return;

            IndexVO index = null;
            for (IndexVO temp : table.getIndexList()) {
                if (temp.getName().equalsIgnoreCase(indexName)) {
                    index = temp;
                    break;
                }
            }
            if (index == null) {
                index = new IndexVO();
                index.setName(indexName);
                index.setUnique((String) getValue("unique", data));
                table.addIndex(index);
            }

            IndexFieldVO field = new IndexFieldVO();
            field.setName(unwrapSymbol((String) getValue("column_name", data)));
            field.setOrderType("1");

            Object ordinal = getValue("ordinal", data);
            field.setOrdinal(ordinal == null ? 99 : Integer.parseInt(ordinal.toString()));
            index.addField(field);
        });
    }

    @Override
    public Map<String, String> getDataTypeMap() {
        return dataTypeMap;
    }

    public String changeType(String fieldType) {
        if (Tool.isBlank(fieldType)) throw new CommonException("未知数据库类型【" + fieldType + "】！");
        String dataType = dataTypeMap.get(fieldType.toLowerCase());
        if (Tool.isBlank(dataType)) throw new CommonException("未知数据库类型【" + fieldType + "】！");
        return dataType;
    }

    public boolean isEqualsType(String dataType, String columnType) {
        return fieldTypeMap.get(dataType) != null && fieldTypeMap.get(dataType).equalsIgnoreCase(columnType);
    }

    public Object getValue(String key, Map<String, Object> data) {
        for (Map.Entry<String, Object> entry : data.entrySet()) {
            if (entry.getKey().equalsIgnoreCase(key)) {
                return entry.getValue();
            }
        }
        return null;
    }

    public String wrapSymbol(String value) {
        if (value.trim().startsWith("`")) return value;
        return "`" + value.trim().toLowerCase() + "`";
    }

    public String unwrapSymbol(String value) {
        if (Tool.isBlank(value)) return value;

        if (value.trim().startsWith("`")) {
            value = value.trim().substring(1);
        }
        if (value.trim().endsWith("`")) {
            value = value.trim().substring(0, value.length() - 1);
        }
        return value;
    }
}
